﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;

namespace PM.Services
{
    public class SpouseServices
    {
        private static Spouse GetSpouseFromDataReader(IDataReader dataReader)
        {
            Spouse retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Spouse();
                retVal.ClientId = dataReader["CLIENT_ID"].ToString();
                retVal.SpouseId = dataReader["SPOUSE_ID"].ToString();
                retVal.Title = dataReader["TITLE"].ToString();
                retVal.Other_Names = dataReader["FULL_NAME"].ToString();
                DateTime dob = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["DOB"].ToString(), out dob))
                {
                    retVal.DoB = dob;
                }
                retVal.Gender = dataReader["GENDER"].ToString();                
                retVal.AddressRes = dataReader["ADDRESS_RES"].ToString();
                retVal.AddressOff = dataReader["ADDRESS_OFF"].ToString();
                retVal.TeleMob = dataReader["TELE_MOB"].ToString();
                retVal.TeleRes = dataReader["TELE_RES"].ToString();
                retVal.TeleOff = dataReader["TELE_OFF"].ToString();
                retVal.Fax = dataReader["FAX"].ToString();
                retVal.Email = dataReader["EMAIL"].ToString();
                retVal.Occupation = dataReader["OCCUPATION"].ToString();
                retVal.Remarks = dataReader["REMARKS"].ToString();                
            }
            return retVal;
        }

        public static Spouse GetSpouseByKeys(string client_id, string spouse_id)
        {
            Spouse retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Spouse where client_id = '" + client_id + "' and spouse_id = '" + spouse_id  + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetSpouseFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static Spouse GetSpouseByClientId(string client_id)
        {
            Spouse retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Spouse where client_id = '" + client_id + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetSpouseFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }


        public static String GetSpouseNameByIds(string client_id, string spouse_id)
        {
            String retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select t.title || '.' || t.full_name as  SPOUSENAME FROM spouse t where t.CLIENT_ID = '" + client_id + "' and t.SPOUSE_ID = '" + spouse_id + "'";
                cmd.Connection = con;

                retVal = cmd.ExecuteScalar().ToString();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static SpouseCollection GetSpouseCollection()
        {
            SpouseCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Spouse";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new SpouseCollection();
                    while (dataReader.Read())
                    {
                        Spouse item = GetSpouseFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetSpouseCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Spouse";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(Spouse spouse)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into Spouse(CLIENT_ID, SPOUSE_ID,TITLE, FULL_NAME, DOB, GENDER, ADDRESS_RES,ADDRESS_OFF," +
                                  "TELE_MOB, TELE_RES, TELE_OFF, FAX, EMAIL, OCCUPATION, REMARKS)" +
                                  "values(:v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8,:v9,:v10,:v11,:v12,:v13,:v14,:v15, :V16 )";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = spouse.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = spouse.SpouseId;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = spouse.Title;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = spouse.Other_Names;
                cmd.Parameters.Add(":v5", OracleType.DateTime).Value = spouse.DoB;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = spouse.Gender;
                
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = spouse.AddressRes;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = spouse.AddressOff;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = spouse.TeleMob;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = spouse.TeleRes;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = spouse.TeleOff;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = spouse.Fax;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = spouse.Email;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = spouse.Occupation;
                cmd.Parameters.Add(":v16", OracleType.VarChar).Value = spouse.Remarks;                

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string client_id, string spouse_id)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from spouse where client_id = '" + client_id + "' and spouse_id = '" + spouse_id + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Spouse spouse)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update spouse set " +
                                  "TITLE = :v3 ," +
                                  "FULL_NAME = :v4 ," +
                                  "DOB = :v5 ," +
                                  "GENDER = :v6 ," +
                                 
                                  "ADDRESS_RES = :v8 ," +
                                  "ADDRESS_OFF = :v9 ," +
                                  "TELE_MOB = :v10 ," +
                                  "TELE_RES = :v11 ," +
                                  "TELE_OFF = :v12 ," +
                                  "FAX = :v13 ," +
                                  "EMAIL = :v14 ," +
                                  "OCCUPATION = :v15 ," +
                                  "REMARKS = :v16 " +
                                  "where client_id = :v1 " +
                                  "and spouse_id = :v2";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = spouse.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = spouse.SpouseId;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = spouse.Title;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = spouse.Other_Names;
                cmd.Parameters.Add(":v5", OracleType.DateTime).Value = spouse.DoB;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = spouse.Gender;
                
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = spouse.AddressRes;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = spouse.AddressOff;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = spouse.TeleMob;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = spouse.TeleRes;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = spouse.TeleOff;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = spouse.Fax;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = spouse.Email;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = spouse.Occupation;
                cmd.Parameters.Add(":v16", OracleType.VarChar).Value = spouse.Remarks;
                
                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
